<?php

class LetterController extends Controller {

    public function actionIndex() {
        echo 'erst letter controller';
    }

    public function actionFilterById() {
        $id = $_GET['id'];
        $condition = '`letter_id`="' . $id . '"';
        $data = $this -> _getLetterBy($condition);
        return $this -> _returnJSON($data);
    }

    private function _returnJSON($data) {
        echo CJSON::encode($data);
    }

    private function _getLetterBy($condition) {
        if (isset($_GET['lfrom']) && isset($_GET['lto'])) {
            $condition .= ' LIMIT ' . $_GET['lfrom'] . ',' . $_GET['lto'] . '';
        }

        $connection = Yii::app() -> db;
        $sql = 'SELECT * FROM `letter` WHERE ' . $condition;
        $command = $connection -> createCommand($sql);
        $dataReader = $command -> query();
        $data = array();
        if (isset($_GET['count'])) {
            array_push($data, count($dataReader));
            return $data;
        }
        foreach ($dataReader as $row) {
            array_push($data, $this -> _getArray($row));
        }
        return $data;
    }

    private function _getArray($row) {
        $arr = array();
        $arr['id'] = $row['letter_id'];
        $arr['year'] = $row['jahr'];
        $arr['month'] = $row['monat'];
        $arr['day'] = $row['tag'];
        $arr['weekday'] = $row['day_n'];
        $arr['from'] = $row['absender'];
        $arr['to'] = $row['adressat'];
        return $arr;
    }

    /**
     * http Anfrage: letters/filter/person/Adam Gurowski
     */
    public function actionFilterByPerson() {
        $name = $_GET['pname'];
        $condition = '`name` LIKE "' . $name . '"';
        $data = $this -> _getLetterByPerson('absender', $condition);
        $data2 = $this -> _getLetterByPerson('adressat', $condition);
        array_merge($data, $data2);
        return $this -> _returnJSON($data);
    }

    /**
     * http Anfrage: letters/filter/person/name/alphabetical/a
     */
    public function actionFilterByAlphabetical() {
        $lett = $_GET['letter'];
        $condition = '`lastname` LIKE "' . $lett . '%"';
        $data = $this -> _getLetterByPerson('absender', $condition);
        $data2 = $this -> _getLetterByPerson('adressat', $condition);
        array_merge($data, $data2);
        return $this -> _returnJSON($data);
    }

    /**
     *
     */
    private function _getLetterByPerson($tbl, $condition) {
        $out = array();
        $connection = Yii::app() -> db;
        $sql = 'SELECT * FROM `' . $tbl . '` WHERE ' . $condition;
        $command = $connection -> createCommand($sql);
        $dataReader = $command -> query();
        foreach ($dataReader as $row) {
            $letter_id = $row['letter_id'];
            $data = $this -> _getLetterBy('`letter_id`="' . $letter_id . '"');
            array_push($out, $data);
        }
        return $out;
    }

    public function actionFilterByAlphabeticalFromTo() {
        $from = $_GET['from'];
        $to = $_GET['to'];
        $az = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z';
        $start = false;
        $condition = '';
        foreach (explode( ',',$az) as $c) {
            if ($c == $from) {
                $start = true;
                $condition = '`lastname` LIKE "' . $c . '%"';
                continue;
            }
            if ($start) {
                $condition .= ' OR `lastname` LIKE "' . $c . '%"';
            }

            if ($c == $to) {
                break;
            }
        }
        $data = $this -> _getLetterByPerson('absender', $condition);
        $data2 = $this -> _getLetterByPerson('adressat', $condition);
        array_merge($data, $data2);
        return $this -> _returnJSON($data);
    }

    public function actionFilterByTime() {
        $condition = $this -> _getTimeCondition(null);
        $data = $this -> _getLetterBy($condition);
        return $this -> _returnJSON($data);
    }

    private function _getTimeCondition($time) {
        if (!isset($time)) {
            $time = $_GET['time'];
        }
        $arr = explode('-', $time);

        $condition = '`jahr` ="' . $arr[0] . '"';
        if (count($arr) > 1) {
            $condition .= ' AND `monat` ="' . $arr[1] . '"';
        }
        if (count($arr) > 2) {
            $condition .= ' AND `tag` ="' . $arr[2] . '"';
        }
        return $condition;
    }

    public function actionFilterByTimeFromTo() {
        $from = $_GET['from'];
        $to = $_GET['to'];
        $from = $this -> _getTimeCondition($from);
        $from = str_replace("=", ">=", $from);
        $to = $this -> _getTimeCondition($to);
        $to = str_replace("=", "<=", $to);
        $condition = '(' . $from . ') AND (' . $to . ')';

        $data = $this -> _getLetterBy($condition);
        return $this -> _returnJSON($data);
    }

    public function actionFilterByPlace() {
        $place = $_GET['place'];
        if (!isset($place)) {
            return;
        }
        $condition = '`abs_ort` LIKE "' . $place . '" OR `adr_ort` LIKE "' . $place . '"';
        $data = $this -> _getLetterBy($condition);
        return $this -> _returnJSON($data);
    }
    
    public function actionFilterByCategory(){
        $category = $_GET['category'];
        if (!isset($category)) {
            return;
        }
        $condition = '`kategorie` LIKE "' . $category.'"';
        $data = $this -> _getLetterBy($condition);
        return $this -> _returnJSON($data);
    }

}
